package net.vsame.url2sql.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;

import net.vsame.url2sql.helper.Url2SqlContext;
import net.vsame.url2sql.helper.WebHelper;
import net.vsame.url2sql.sql.Model;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 2011-11-3 下午08:15:02
 * @author <a href="mailto:gaollg@sina.com">Gaollg</a>
 */
public class JdbcUtils {
	
	private static Log LOG = LogFactory.getLog(JdbcUtils.class);
	private static String className = "com.mysql.jdbc.Driver";
	private static String url = null;
	private static String username = null;
	private static String password = null;
	
	static {
		try {
			loadJDBC();
		} catch (Exception e) {
			LOG.error("JDBC驱动加载失败：" + e.getMessage(), e);
		}
	}
	
	private static void loadJDBC() throws Exception{
		Properties retVal = JarUtils.loadProperties(JarUtils.getResourceAsStreamFromClassPath("jdbc.properties"));
		if(retVal == null) {
			return;
		}
		className = retVal.getProperty("className");
		url = retVal.getProperty("url");
		username = retVal.getProperty("username");
		password = retVal.getProperty("password");
		Class.forName(className);
	}
	
	/**
	 * 取得连接
	 * @param url
	 * @param username
	 * @param password
	 * @return
	 */
	public static Connection getConnection(String url, String username, String password){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
		return conn;
	}
	
	/**
	 * 从本地取得连接
	 * @return
	 */
	public static Connection getConnection(){
		return getConnection(url, username, password);
	}
	
	/**
	 * 关闭连接,释放资源
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public static void free(ResultSet rs, Statement stmt, Connection conn) {
		try { // 捕捉异常
			try {
				if (rs!=null) { // 当ResultSet对象的实例rs不为空时
					rs.close(); // 关闭ResultSet对象
				}
			} finally {
				try {
					if (stmt!=null) { // 当Statement对象的实例stmt不为空时
						stmt.close(); // 关闭Statement对象
					}
				}catch (Exception e) {
					e.printStackTrace();
				} 
				finally {
					if (conn!=null) { // 当Connection对象的实例conn不为空时
						conn.close(); // 关闭Connection对象
					}
				}
			}
		} catch (Exception e) {
		}
	}

	public static void free(ResultSet rs, Statement stmt) {
		free(rs, stmt, null);
	}
	
	/**
	 * 给PreparedStatement赋值
	 * @param pstmt
	 * @param values
	 * @throws SQLException
	 */
	private static void setPstmtValues(PreparedStatement pstmt, Object... values)
			throws SQLException {
		if(pstmt!=null&values!=null){
			for (int i = 0; i < values.length; i++) {
				pstmt.setObject(i+1, values[i]);
			}
		}
	}
	/**
	 * 执行SQL语句(insert,update,delete)
	 * @param callBack 回调函数中rs可获取[由于执行此 Statement 对象]而创建的所有自动生成的键
	 * @param sql sql语句
	 * @param values 对就占位符的值
	 * @return 返回影响的行数
	 */
	public static int execute(JdbcUtils.CallBack callBack, String sql, Object... values){
		LOG.debug(sql + "|" + Arrays.toString(values));
		Connection conn = WebHelper.getContext().getConn();
		int retVal = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			if(callBack != null){
				pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			}else{
				pstmt = conn.prepareStatement(sql);
			}
			
			setPstmtValues(pstmt, values);
			retVal = pstmt.executeUpdate();
			
			if(callBack!=null){
				rs = pstmt.getGeneratedKeys();
				callBack.callback(rs);//回调
			}
		}catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			JdbcUtils.free(rs, pstmt);
		}
		return retVal;
	}
	
	/**
	 * 执行SQL语句(insert,update,delete)
	 * @param sql sql语句
	 * @param values 对就占位符的值
	 * @return 返回影响的行数
	 */
	public static int execute(String sql, Object... values){
		return execute(null, sql, values);
	}
	
	/**
	 * 回调一次 未经过任何处理的ResultSet
	 * @param callBack
	 * @param sql
	 * @param values
	 * @see JdbcUtils.query()
	 */
	public static void query(JdbcUtils.CallBack callBack, String sql, Object... values){
		LOG.debug(sql + "|" + Arrays.toString(values));
		Connection conn = WebHelper.getContext().getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sql);
			setPstmtValues(pstmt, values);
			rs = pstmt.executeQuery();
			callBack.callback(rs);
		}catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			JdbcUtils.free(rs, pstmt);
		}
	}
	
	/**
	 * JdbcUtils回调接口
	 * 2011-11-3 下午08:29:15
	 * @author <a href="mailto:gaollg@sina.com">Gaollg</a>
	 *
	 */
	public static interface CallBack{
		/**
		 * 回调方法
		 * @throws SQLException 
		 */
		public void callback(ResultSet rs) throws SQLException;
	}
	
	/**
	 * 强制初始化 表信息
	 * @param conn
	 * @return 
	 * @throws Exception
	 */
	public static List<String> getAllTables(){
		Connection conn = WebHelper.getContext().getConn();
		String[] types = {"TABLE"};
		List<String> tables = new ArrayList<String>();
		DatabaseMetaData data;
		ResultSet rs = null;
		try {
			data = conn.getMetaData();
			rs = data.getTables(null, null, null, types);
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				tables.add(tableName);
			}
		}catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			JdbcUtils.free(rs, null);
		}
		return tables;
	}
	
	public static Long executeGeneratedKey(String sql, Object... values){
		final Long[] longs = new Long[1];
		JdbcUtils.execute(new JdbcUtils.CallBack() {
			
			@Override
			public void callback(ResultSet rs) throws SQLException {
				//循环并注入
				if(rs.next()){
					try {
						longs[0] = rs.getLong(1);
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				}
			}
		}, sql, values);
		return longs[0];
	}
	
	/**
	 * 查询实体列表
	 * @param <T> 
	 * @param sql
	 * @param values
	 * @return
	 */
	public static List<Model> queryList(String sql, Object... values){
		final List<Model> list = new ArrayList<Model>();
		JdbcUtils.query(new JdbcUtils.CallBack() {
			public void callback(ResultSet rs) throws SQLException {
				//取得列名
				ResultSetMetaData data = rs.getMetaData();
				/** 数据库中的列  */
				List<String> dataColumns = new ArrayList<String>();
				for(int i=0;i<data.getColumnCount();i++){
					dataColumns.add(data.getColumnLabel(i+1));
				}
				//循环并注入
				while(rs.next()){
					Model m = new Model();
					try {
						for(String key : dataColumns){
							m.put(key, rs.getObject(key));
						}
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
					list.add(m);
				}
			}
			
		}, sql, values);
		return list;
	}
	
	/**
	 * 查询实体
	 * @param <T>
	 * @param sql
	 * @param values
	 * @return
	 */
	public static Model queryOne(String sql, Object... values){
		List<Model> list = queryList(sql, values);
		if(list.size()==0){
			return null;
		}
		return list.get(0);
	}
	
	/**
	 * 查询个数
	 * @param sql
	 * @param values
	 * @return
	 */
	public static long queryCount(String sql, Object... values) {
		final List<Long> list = new ArrayList<Long>();
		JdbcUtils.query(new JdbcUtils.CallBack() {
			public void callback(ResultSet rs) throws SQLException {
				if(rs.next()){
					list.add(rs.getLong(1));
				}
			}
		}, sql, values);
		return list.get(0);
	}
	
	public static PageView queryPage(int currentpage, int pagesize, String column, String where, Object... values){
		PageView v = new PageView(pagesize, currentpage);
		long count = queryCount("select count(*) " + where, values);
		v.setTotal(count);
		
		if(v.calcFirstResult() < count){
			String sql = column + " " + where + " limit " + v.calcFirstResult() + "," + v.getPagesize();
			List<Model> datas = queryList(sql, values);
			v.setDatas(datas);
		}else{
			v.setDatas(new ArrayList<Model>());
		}
		return v;
	}
	
	public static void main(String[] args) throws Exception {
		WebHelper.init(null, null);
		Url2SqlContext c = WebHelper.getContext();
		c.getConn().setAutoCommit(true);
		
		PageView page = queryPage(3, 4, "select id", "from system_notice where 1=2");
		System.out.println(page);
	}
	
}
